﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using System.Data;
using System.IO;
using System.Configuration;
using System.Xml;

namespace JLT.Countdown.Models
{
    public class BusStop
    {
        public string getData(string latitude, string longitude)
        {
            //string latitude = latlon.Split(',')[0];
            //string longitude = latlon.Split(',')[1];
            
            string connstring = ConfigurationManager.ConnectionStrings["gisdb"].ConnectionString;
           
            NpgsqlConnection conn = new NpgsqlConnection(connstring);

            NpgsqlTransaction t = null;

            DataSet dsStops = new DataSet();

            try
            {
                conn.Open();

                t = conn.BeginTransaction();

                //select * from nearest_stops_detail('51.4027','-0.2653');

                NpgsqlCommand command = new NpgsqlCommand("select * from nearest_stops_detail(:latitude,:longitude)", conn);

                // Now add the parameter to the parameter collection of the command specifying its type.
                command.Parameters.Add(new NpgsqlParameter("latitude", NpgsqlTypes.NpgsqlDbType.Text));
                command.Parameters.Add(new NpgsqlParameter("longitude", NpgsqlTypes.NpgsqlDbType.Text));

                command.Prepare();

                command.Parameters[0].Value = latitude;
                command.Parameters[1].Value = longitude;

                command.CommandType = CommandType.StoredProcedure;

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
                da.Fill(dsStops);
            }
            finally
            {
                t.Commit();
                conn.Close();
            }

            StringWriter sw = new StringWriter();
            XmlWriter xw = XmlWriter.Create(sw);
           
            dsStops.WriteXml(xw);

            return sw.ToString();
        }
    }
}
